<?php
require_once("../lib/clDate.php");
$arrSal = 
array(
array('name'=>'ORU', 'salaries' => array(
	array("Damien", "BOREL"),
	array("Emmanuel", "CERVETTI"),
	array("Virginie", "CHAON"),
	array("Marion", "DUPREY"),
	array("Maurine", "FERNANDEZ"),
	array("Maurine", "FERNANDEZ 0.4"),
	array("Thibault", "Hyvernat"),
	array("Ann", "METIVIER"),
	array("Laetitia", "MONTEIRO"),
	array("Karine", "RIANT")
)), 
array('name'=>'GCS', 'salaries' => array( 
	array("Erika", "BOREL"), 
	array("Maximes", "SORRIANO"),
	array("Valérie", "GAUTHIER"),
	array("Veronique", "DAVID")
)));

// on se connecte à MySQL 
$db = mysql_connect('localhost', 'orupaca', 'Cpqr3zCnt') or die('connexion sql'); 
mysql_select_db('orupaca_sop',$db) or die('select db'); 


foreach ($arrSal as $arrStruct){
	echo "++ GENERATION ".$arrStruct['name']." ++<br /><br />";
	foreach ($arrStruct['salaries'] as $arrSalarie){
		$arrDates = array(
			"2008-01" => crea_date(), "2008-02"=> crea_date(), "2008-03"=> crea_date(), "2008-04"=> crea_date(), "2008-05"=> crea_date(), "2008-06"=> crea_date(), "2008-07"=> crea_date(), "2008-08"=> crea_date(), "2008-09"=> crea_date(), "2008-10"=> crea_date(), "2008-11"=> crea_date(), "2008-12"=> crea_date(),
			"2009-01"=> crea_date(), "2009-02"=> crea_date(), "2009-03"=> crea_date(), "2009-04"=> crea_date(), "2009-05"=> crea_date(), "2009-06"=> crea_date(), "2009-07"=> crea_date(), "2009-08"=> crea_date(), "2009-09"=> crea_date(), "2009-10"=> crea_date(), "2009-11"=> crea_date(), "2009-12"=> crea_date(),
			"2010-01"=> crea_date(), "2010-02"=> crea_date(), "2010-03"=> crea_date());
		
		
		echo  $arrSalarie[0] . " " . $arrSalarie[1] . " --> " . 'uploads/extraction/' . $arrStruct['name'] . '_'.$arrSalarie[0].'_'.$arrSalarie[1].'.csv<br /><br />';
		
		
		$file = fopen('uploads/extraction/' . $arrStruct['name'] . '_'.$arrSalarie[0].'_'.$arrSalarie[1].'.csv', "w+");
		fwrite($file, "Mois;CA;Recup;Heures sup;Payees\n");
		$sql = "SELECT id, nb_heures_semaine as hsemaine FROM salarie WHERE nom='".$arrSalarie[1]."' AND prenom='" . $arrSalarie[0] ."'";
		$res = mysql_query($sql) or die($sql);
		while ($row = mysql_fetch_object($res)) {
			$id = $row->id;
			$iHSemaine = $row->hsemaine;
		}
		
		$sql = "SELECT date FROM jour WHERE is_valide = 1 AND worker_id = " .$id . " AND type = 4";
		$res = mysql_query($sql) or die ($sql);
		while ($row = mysql_fetch_object($res)){
			$iAnnee = substr($row->date, 0, 4);
			$iMois = substr($row->date, 5, 2);
			if (! isset($arrDates[$iAnnee . "-" . $iMois])){
				$arrDates[$iAnnee . "-" . $iMois] = crea_date();
			}
			$arrDates[$iAnnee . "-" . $iMois]['ca'] += 1;
		}
		
		$sql = "SELECT date FROM jour WHERE is_valide = 1 AND worker_id= " .$id." AND type=5";
		$res = mysql_query($sql) or die ($sql);
		while ($row = mysql_fetch_object($res)){
			$iAnnee = substr($row->date, 0, 4);
			$iMois = substr($row->date, 5, 2);
			if (! isset($arrDates[$iAnnee . "-" . $iMois])){
				$arrDates[$iAnnee . "-" . $iMois] = crea_date();
			}
			$arrDates[$iAnnee . "-" . $iMois]['rec'] += 1;
		}
		
		$sql = "SELECT sum(total_journee)/60 as total, date FROM jour WHERE is_valide = 1 AND worker_id= " .$id." AND type=6 GROUP BY date";
		$res = mysql_query($sql) or die ($sql);
		while ($row = mysql_fetch_object($res)){
			$iAnnee = substr($row->date, 0, 4);
			$iMois = substr($row->date, 5, 2);
			if (! isset($arrDates[$iAnnee . "-" . $iMois])){
				$arrDates[$iAnnee . "-" . $iMois] = crea_date();
			}
			$arrDates[$iAnnee . "-" . $iMois]['hsp'] += $row->total;
		}
		
		
		
		
		
		foreach ($arrDates as $iDate => $arrCounts) {
		$nbJour = 0;
		$m = substr($iDate, 5, 2);
	$a = substr($iDate, 0, 4);
	$a1 = ($m == 12 ? $a + 1 : $a);
	$m1 = $m+1;
	$m1 = ($m1==13 ? 1 : $m1);
	$m1 = ($m1 < 10 ? '0' . $m1 : '' . $m1);
			$debutM = new clDate("01/" . $m . "/" . $a);
			$finM = new clDate("01/" . $m1 . "/" . $a1);
			while ($debutM->getDatetime() != $finM->getDatetime()){
				if ($debutM->getDayWeekNumber() != 6 && $debutM->getDayWeekNumber() != 0) $nbJour += 1 ;
				$debutM->addDays(1);
			}
			$nbHMois = $nbJour * ($iHSemaine/5)*60* -1;
			$sql = "SELECT total_journee as total FROM jour WHERE is_valide= 1 AND worker_id = " . $id . " AND ";
			$sql .= "date >= '" . $iDate . "-01' AND date <= '" . $iDate . "-31'" ;
			$total = 0;
			$res = mysql_query($sql) or die ($sql);
			while ($row = mysql_fetch_object($res)){
				$total += $row->total;
			}
		
			echo $iDate . " : ca => " . $arrCounts['ca'] . " ; recup => " . $arrCounts['rec'] . "; heures_sup => " .($total + $nbHMois) / 60 . " ; heures_sup_payees => " .$arrCounts['hsp'] . " <br />";
			//"Mois;CA;Recup;Heures sup;Heures sup payees\n"
			$iHSup = ($total + $nbHMois);
			$strH = "" . (int)($iHSup / 60) . "h";
			$iMin = (($iHSup/60%60) < 0 ? ($iHSup/60%60*-1) : ($iHSup/60%60));
			$strMin = ($iMin < 10 ? "0" . $iMin : "" . $iMin);
			$strHSup = $strH . $strMin;
			fwrite($file, $iDate . ";" . $arrCounts['ca'] . ";" . $arrCounts['rec'] . ";" . $strHSup . ";" . $arrCounts['hsp'] . "\n");
		}
		

		fclose($file);
	}
	
}
// on ferme la connexion à mysql 
mysql_close(); 

function crea_date(){
	$arrRet = array();
	$arrRet['ca'] = 0;
	$arrRet['rec'] = 0;
	$arrRet['hsp'] = 0;
	$arrRet['hs'] = 0;
	
	return $arrRet;
}
?>
